﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics.Contracts;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web.Mvc;

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using ProjectBase.Core;

namespace ProjectBase.Tools.Import.Excel
{
    public static class Extensions
    {
        #region GetValue
        public static string GetValue(this Cell cell, SharedStringTable sharedStringTable)
        {
            if (cell.IsNull() || sharedStringTable.IsNull() || cell.CellValue.IsNull())
            {
                return null;
            }

            var index = 0;
            if (cell.DataType.Is()
             && cell.DataType.Value.Equals(CellValues.SharedString))
            {
                index = Convert.ToInt32(cell.CellValue.Text);
            }
            else if (cell.DataType.IsNull()
            || cell.DataType.Value.Equals(CellValues.Number)
            || cell.DataType.Value.Equals(CellValues.Date))
            {
                int intValue;
                if (int.TryParse(cell.CellValue.Text, NumberStyles.Any, CultureInfo.InvariantCulture, out intValue))
                {
                    return intValue.ToString(CultureInfo.InvariantCulture);
                }
                decimal decimalValue;
                if (decimal.TryParse(cell.CellValue.Text, NumberStyles.Any, CultureInfo.InvariantCulture, out decimalValue))
                {
                    return decimalValue.ToString(CultureInfo.InvariantCulture);
                }
            }
            else
            {
                return cell.CellValue.Text;
            }

            var items = sharedStringTable.Elements<SharedStringItem>();
            if (items.Count() > index && index >= 0)
            {
                var item = items.ElementAt(index);
                return item.Text.Text;
            }
            return null;
        }
        #endregion GetValue

        #region GetColumn
        public static string GetColumnName(string cellName)
        {
            var name = cellName ?? string.Empty;
            // Create a regular expression to match the column name portion of the cell name.
            var regex = new Regex("[A-Za-z]+");
            var match = regex.Match(name);

            return match.Value;
        }
        public static string GetColumnName(this Cell cell)
        {
            return GetColumnName(cell.CellReference);
        }
        #endregion GetColumn
    }
}
